package org.example.practiceexamples.repository;


import org.example.practiceexamples.dox.User;
import org.example.practiceexamples.dto.UserAppointment;
import org.example.practiceexamples.dto.UserCourse;
import org.example.practiceexamples.dto.UserLab;
import org.example.practiceexamples.mapper.UserAppointmentResultSetExtractor;
import org.example.practiceexamples.mapper.UserCourseResultSetExtractor;
import org.example.practiceexamples.mapper.UserLabResultSetExtractor;
import org.springframework.data.jdbc.repository.query.Modifying;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface UserRepository extends CrudRepository<User, String> {

    @Query("""
        select * from user u where u.account=:account
    """)
    User findUserByAccount(String account);

    @Query("""
        select * from user
    """)
    List<User> findAllUsers();

    @Query("""
        select password from user where account = :account
    """)
    String findPasswordByAccount(String account);

    @Query("""
        select role from user where account = :account
    """)
    String findRoleByAccount(String account);

    @Query("""
        select status from user where account = :account 
    """)
    int findStatusByAccount(String account);

    @Query("""
        select id from user where account = :account
    """)
    String findIdByAccount(String account);

    @Query("""
        select * from user where id = :uid
    """)
    User findUserByUid(String uid);

    @Modifying
    @Query("""
        update user set password = :pwd where id = :id
    """)
    void updatePasswordById(String id, String pwd);

    @Modifying
    @Query("""
        update user set profession = :newProfession where id = :id
    """)
    void updateProfessionById(String id, String newProfession);

    @Modifying
    @Query("""
        update user set phone = :newPhone where id = :id
    """)
    void updatePhoneById(String id, String newPhone);

    @Modifying
    @Query("""
         update user set description = :newDescription where id = :id
    """)
    void updateDescriptionById(String id, String newDescription);



    @Modifying
    @Query("""
        update user set password = :pwd where account = :account
    """)
    void updatePasswordByAccount(String account, String pwd);

    @Modifying
    @Query("""
        update user set profession = :newProfession where account = :account
    """)
    void updateProfessionByAccount(String account, String newProfession);

    @Modifying
    @Query("""
        update user set phone = :newPhone where account = :account
    """)
    void updatePhoneByAccount(String account, String newPhone);

    @Modifying
    @Query("""
         update user set description = :newDescription where account = :account
    """)
    void updateDescriptionByAccount(String account, String newDescription);

    @Modifying
    @Query("""
         update user set role = :newRole where account = :account
    """)
    void updateRoleByAccount(String account, String newRole);

    @Modifying
    @Query("""
         update user set role = :newRole where id = :id
    """)
    void updateRoleById(String id, String newRole);

    @Modifying
    @Query("""
         update user set status = :newStatus where account = :account
    """)
    void updateStatusByAccount(String account, int newStatus);

    @Modifying
    @Query("""
         update user set status = :newStatus where id = :id
    """)
    void updateStatusById(String id, int newStatus);

    @Modifying
    @Query("""
        delete from user where account = :account
    """)
    void deleteUserByAccount(String account);


    @Query(value = """
        select * from user u join appointment a on u.id = a.teacher->>'$.id' where u.id = :uid
    """,resultSetExtractorClass = UserAppointmentResultSetExtractor.class)
    UserAppointment findUserAppointmentByUid(String uid);

    @Query(value = """
        select * from user u join appointment a on u.id = a.teacher->>'$.id' where u.account= :account
    """,resultSetExtractorClass = UserAppointmentResultSetExtractor.class)
    UserAppointment findUserAppointmentByAccount(String account);

    @Query(value = """
        select * from user u join course c on u.id = c.teacher_id where u.id=:uid
    """,resultSetExtractorClass = UserCourseResultSetExtractor.class)
    UserCourse findUserCourse(String uid);

    @Query(value = """
        select * from user u join course c on u.id = c.teacher_id where u.account = :account
    """,resultSetExtractorClass = UserCourseResultSetExtractor.class)
    UserCourse findUserCourses(String account);

    @Query(value= """
        select * from user u join lab l on u.name = l.manage where u.id = :uid 
    """,resultSetExtractorClass = UserLabResultSetExtractor.class)
    UserLab findUserLabByUid(String uid);

    @Query(value= """
        select * from user u join lab l on u.name = l.manage where u.account = :account
    """,resultSetExtractorClass = UserLabResultSetExtractor.class)
    UserLab findUserLabByAccount(String account);



}
